package com.auxiliary.upload_download;

import java.io.File;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;

public class upload {
    public static void uploadFile(MultipartFile file) throws Exception{
        //1:创建workbook
        Workbook workbook=Workbook.getWorkbook(file.getInputStream());
        //2:获取第一个工作表sheet
        Sheet sheet=workbook.getSheet(0);
        //3:获取数据
        Connection conn = ConnectDB.getConn();
        String sql =  "INSERT into quota_data (frequency, time, quota, dataitem, value, unit) "
        			+ "VALUES (?,?,?,?,?,?)";
        PreparedStatement pstmt;
        pstmt = (PreparedStatement) conn.prepareStatement(sql);

        for(int i = 1; i < sheet.getRows(); i++){
		    for(int j = 0; j < sheet.getColumns(); j++){
		        Cell cell=sheet.getCell(j,i);
                    pstmt.setString(j+1, cell.getContents());
		    }
            pstmt.executeUpdate();
		} 

        pstmt.close();
        conn.close();
        //最后一步：关闭资源
        workbook.close();
    }

}
